set hive.exec.dynamic.partition= true;
set hive.exec.dynamic.partition.mode= 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode= 200;
set hive.exec.max.dynamic.partitions=200 ;
with plat_data as (
select
    manage_code,                   --管理大区code
    max(manage_name) as manage_name,--管理大区名字
    agent_code,                     --代理区code
    max(agent_name) as agent_name,  --代理区名字
    sum(case when ordersource_name='七星潭' then billCount else 0 end ) as qxt_bill_count, --七星潭平台业务量
    sum(case when ordersource_name='桃花岛' then billCount else 0 end ) as thd_bill_Count, --桃花岛平台业务量
    sum(case when ordersource_name='紫金山' then billCount else 0 end ) as zjs_bill_Count, --紫金山平台业务量
    sum(case when ordersource_name='逍遥峰' then billCount else 0 end ) as xyf_bill_Count, --逍遥峰平台业务量
    sum(case when ordersource_name='极地湾' then billCount else 0 end ) as jdw_bill_Count, --极地湾平台业务量
    sum(case when ordersource_name !='七星潭' and ordersource_name !='桃花岛' and ordersource_name !='紫金山'
                  and ordersource_name !='逍遥峰' and ordersource_name !='极地湾' then  billCount else 0 end ) as qt_bill_Count, --其它平台业务量
     sum(billCount) as pick_waybill_cnt,     --收件量
       dt                                    --日期
from jms_dm.dm_order_source_sum_dt
where dt>DATE_ADD('{{ execution_date | cst_ds }}',-3)
and dt<='{{ execution_date | cst_ds }}'
and agent_code is not null
--and agent_code <> 888888
group by manage_code,agent_code,dt
),
trunk_departure_data as(
 select
      dt,                                                      --发车日期
     start_agent_code,                      --发车代理区code
     max(b.start_agent_name) start_agent_name,                 --发车代理区名字
     sum(case when b.islate = '是'  then 1 else 0 end) not_punctuality_sum, --非准点车数
     sum(case when b.islate = '否'  then 1 else 0 end) punctuality_sum,   --准点车数
     count(0) departure_total                                                               --发车总数
 from(
      select
         a.shipment_no ,
         a.vehicleline_name,
         a.line_name,
         a.start_agent_code,
         a.start_agent_name,
         to_date(a.planned_departure_time) as dt,
         a.actual_departure_time ,
         a.islate,
        -- row_number() over(partition by a.shipment_no,substr(a.start_agent_name,instr(a.start_agent_name,'-')+1) order by a.planned_departure_time) rn
         row_number() over(partition by a.shipment_no,a.start_agent_code order by a.planned_departure_time,a.shipment_no,a.line_name) rn
      from jms_dm.dm_design_vehicle_ontime_detail_dt a
         WHERE a.dt >= date_sub('{{ execution_date | cst_ds }}',15)
         and a.dt <= '{{ execution_date | cst_ds }}'
         ) b where rn = 1
group by b.dt,b.start_agent_code
),
trunk_arrival_data as(
 select
      dt,
     end_agent_code,
     max(b.end_agent_name) end_agent_name,
     sum(case when b.islate = '是'  then 1 else 0 end) arrival_late_sum, --晚点数
     sum(case when b.islate = '否'  then 1 else 0 end) arrival_not_late_sum,                 --非晚点数
     count(0) arrival_total                                                               --总数
  from(
         select
             a.shipment_no ,                          --任务号
             a.vehicleline_name,                      --车辆线路名字
             a.line_name,                             --线路名字
             a.end_agent_code,                        --到达代理区code
             a.end_agent_name,                         --到达代理区
             to_date(a.planned_arrival_time) as dt,     --计划到达时间
             a.actual_arrival_time ,                    --实际到达时间
             a.islate,                                   --是否晚点
            -- row_number() over(partition by a.shipment_no,substr(a.end_agent_name,instr(a.end_agent_name,'-')+1) order by a.planned_arrival_time) rn
             row_number() over(partition by a.shipment_no,end_agent_code order by a.planned_departure_time,a.shipment_no,a.line_name) rn
         from jms_dm.dm_design_vehicle_ontime_detail_dt a
            WHERE a.dt >= date_sub('{{ execution_date | cst_ds }}',15)
            and a.dt <= '{{ execution_date | cst_ds }}'
             ) b where rn = 1
          group by b.dt,b.end_agent_code
),
sign_data as(
select
a.dt,                                    --日期
b.subordinate_agent_code,                --代理区code
max(b.subordinate_agent_name) as subordinate_agent_name,     --代理区名字
sum(a.deliver_waybill_sum) as deliver_waybill_sum,          --派件总量
sum(a.sign_cnt) as sign_cnt                                 --签收总量
from (select
      to_date(deliver_later_scantime) as dt, --最后一次派件时间
      deliver_later_scansitecode,           --最后一次派件网点code
      count( waybill_no) as deliver_waybill_sum,   --派件数量
      sum(case when nvl(outstore_later_scantime,sign_later_scantime)>deliver_later_scantime and to_date(nvl(sign_later_scantime,outstore_later_scantime))=TO_DATE(deliver_later_scantime) and (deliver_later_scansitecode=sign_later_scansitecode or outstore_later_scansitecode = deliver_later_scansitecode) then 1 else 0 end )
        as    sign_cnt                                      --签收数量
      from jms_dwd.dwd_wide_unsign_summary_waybill_dt
      where dt>=date_add('{{ execution_date | cst_ds }}',-30)
      and dt<='{{ execution_date | cst_ds }}'
      group by to_date(deliver_later_scantime),deliver_later_scansitecode
) a
join (
select
      code,                      --网点编码
      name,                      --网点名字
      subordinate_agent_name,    --所属代理区
      subordinate_agent_id,      --所属代理区ID
      subordinate_agent_code    --所属代理区code
 from jms_dim.dim_lmdm_sys_network_expand
) b
on a.deliver_later_scansitecode=b.code
group by a.dt,b.subordinate_agent_code
)
INSERT OVERWRITE TABLE jms_dm.dm_agent_operate_bi_dt PARTITION (dt)
SELECT
a.manage_code,     --管理大区code
a.manage_name,     --管理大区名字
a.agent_code,      --代理区code
a.agent_name,      --代理区名字
a.qxt_bill_count,  --七星潭平台业务量
a.thd_bill_count,  --桃花岛平台业务量
a.zjs_bill_count,  --紫金山平台业务量
a.xyf_bill_count,  --逍遥峰平台业务量
a.qt_bill_count,   --其它平台业务量
a.pick_waybill_cnt, --收件量
b.departure_total,    --发车总个数
b.punctuality_sum,  --准时发车个数
b.not_punctuality_sum,  --晚点发车个数
c.arrival_total,        -- 干线到车数
c.arrival_late_sum,     ---干线到车晚点数
c.arrival_not_late_sum,  --干线准时到车个数
d.deliver_waybill_sum,    --派件总量
d.sign_cnt,               --签收总量
null as new_network_cnt,  --新增网点个数
null as total_network_cnt, --网点总数
null as not_operate_network_cnt,   --未运营网点数
a.dt as business_date,                --日期
a.jdw_bill_count,      --极地湾平台业务量
a.dt                           --日期
from plat_data a                        --平台业务量数据
left join trunk_departure_data b        --干线发车数据
on a.agent_code=b.start_agent_code
and a.dt=b.dt
left join trunk_arrival_data c          --干线到车数据
on a.agent_code=c.end_agent_code
and a.dt=c.dt
left join sign_data d                   --操作派签数据
on a.agent_code=d.subordinate_agent_code
and a.dt=d.dt
distribute by 2
;